package map;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import map.graph.MapGraph;
import map.graph.MapGraphConstants;

import com.taxi.server.carsearch.graph.Node;
import com.taxi.server.carsearch.graph.Way;

public class MapGraphDatabase implements MapGraphConstants {

	private static MapGraphDatabase instance;
	private Connection con;

	/**
	 * Using Singleton pattern.
	 * 
	 * @return
	 */
	public static MapGraphDatabase getInstance() {
		if (instance == null) {
			instance = new MapGraphDatabase();
			instance.connect();
		}
		return instance;
	}

	/**
	 * Connects database instance to the database.
	 */
	private void connect() {
		try {
			String driver = "org.gjt.mm.mysql.Driver";
			Class.forName(driver);
			con = null;
			String url = "jdbc:mysql://localhost:3306/geo";
			con = DriverManager.getConnection(url, "root", "sqlpassword");
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	public MapGraph loadMapGraph() {
		MapGraph graph = new MapGraph(MapGraph.MINLAT, MapGraph.MINLON,
				MapGraph.MAXLAT, MapGraph.MAXLON);

		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select * from nodes");
			while (rs.next()) {
				graph.addNode(new Node(rs.getInt(1), rs.getDouble(2), rs
						.getDouble(3)));
			}
			rs.close();
			ResultSet rs2 = stmt
					.executeQuery("select id, nd1_id, nd2_id, max_speed, oneway from ways");
			while (rs2.next()) {
				Node n1 = graph.getNode(rs2.getInt(2));
				Node n2 = graph.getNode(rs2.getInt(3));
				n1.setState(0);
				n2.setState(0);
				Way way;
				int oneway = rs2.getInt(5);
				switch (oneway) {
				case -1:
					way = new Way(rs2.getInt(1), n2, n1, rs2.getInt(4));
					n2.getWays().add(way);
					graph.addWay(way);
					break;
				case 0:
					way = new Way(rs2.getInt(1), n2, n1, rs2.getInt(4));
					graph.addWay(way);
					n2.getWays().add(way);
					way = new Way(rs2.getInt(1), n1, n2, rs2.getInt(4));
					graph.addWay(way);
					n1.getWays().add(way);
					break;
				case 1:
					way = new Way(rs2.getInt(1), n1, n2, rs2.getInt(4));
					graph.addWay(way);
					n1.getWays().add(way);
					break;

				}

			}

			rs2.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return graph;
	}

	public void deleteNodes(List<Node> nodes) {
		try {
			Statement stmt = con.createStatement();

			con.setAutoCommit(false);

			for (Node n : nodes) {
				stmt.addBatch("delete from nodes where id=" + n.getId());
			}
			stmt.executeBatch();
			con.commit();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void deleteDoubleWays(Collection<Node> nodes) {
		try {
			Statement stmt = con.createStatement();

			con.setAutoCommit(false);

			for (Node n : nodes) {
				List<Way> ways = n.getWays();
				for (Way way : ways) {
					if (!way.deleted) {
						way.deleted = true;

						for (Way copy : ways) {
							if (copy.getNode2() == way.getNode2()) {
								copy.deleted = true;
								stmt.addBatch("delete from ways where id="
										+ copy.getId());
							}
						}
					}

				}
			}
			stmt.executeBatch();
			con.commit();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void addOperativeData(Way way, int time) {
		long curtime = System.currentTimeMillis() / 1000;
		int id = way.getId();
		int hours = new Date().getHours();

		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate("update ways set opdata='" + time
					+ "', opdatatime ='" + curtime + "' where id='" + id + "'");
			stmt.close();
			Statement stmt2 = con.createStatement();
			ResultSet rs = stmt2.executeQuery("select t" + hours
					+ " from ways where id='" + id + "'");
			rs.next();
			int V = rs.getInt(1);
			if (V == 0) {
				V = N * time;
			} else {
				V = V + time - V / N;
			}
			stmt2.close();
			Statement stmt3 = con.createStatement();
			stmt3.executeUpdate("update ways set t" + hours + " = '" + V
					+ "' where id = '" + id + "'");
			stmt3.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public int getTime(Way way) {
		long curtime = System.currentTimeMillis() / 1000;
		int id = way.getId();
		int hours = new Date().getHours();

		try {
			Statement stmt = con.createStatement();
			boolean norm = true;
			if (hours < 6 || hours > 23) {
				hours = 6;
				norm = false;
			}
			ResultSet rs = stmt
					.executeQuery("select opdata, opdatatime, max_speed, t"
							+ hours + " from ways where id='" + id + "'");
			rs.next();
			int opdata = rs.getInt(1);
			int opdatatime = rs.getInt(2);
			int max_speed = rs.getInt(3);
			int t = rs.getInt(4);
			stmt.close();
			if (curtime - opdatatime < 600)
				return opdata;
			else if (norm && t != 0)
				return t;
			else
				return (int) (3.6 * 2 * way.getLength() / max_speed);

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	public void updateDriverPosition(double lon, double lat, String login) {
		try {
			Statement stmt = con.createStatement();
			stmt.executeUpdate("update driver set lon='" + lon + "', lat='"
					+ lat + "' where login='" + login + "'");
			stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<Driver> getDrivers() {
		List<Driver> drivers = new LinkedList<Driver>();

		try {
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select * from driver");
			while (rs.next()) {
				drivers.add(new Driver(rs.getString(1), rs.getString(2), rs
						.getDouble(3), rs.getDouble(4)));
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return drivers;
	}

}
